import cn.weiyang.entity.Dept;
import cn.weiyang.entity.Emp;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.cfg.Configuration;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2017/7/10.
 */
public class Demo {
    Session session=null;
    Transaction tr=null;
    @Before
    public void setUp(){
        Configuration config=new AnnotationConfiguration().configure();
        SessionFactory factory=config.buildSessionFactory();
        session=factory.openSession();
        tr=session.beginTransaction();
    }
    @Test
    /**
     * 查询所有员工的姓名
     */
    public void test1(){
        String hql="from Emp";
        Query query=session.createQuery(hql);
        List<Emp> emps=query.list();
        for(Emp e:emps){
            System.out.println(e.getEname());
        }
    }

    /**
     * 根据员工姓名和入职日期查询员工信息
     */
    @Test
    public void test2(){
        String hql="from Emp e where e.ename like '%林%' and e.hiredate >'2017-1-1'" ;
        Query query=session.createQuery(hql);
        List<Emp> emps=query.list();
        for (Emp emp:emps){
            System.out.println(emp.getEname()+" "+emp.getHiredate());
        }
    }

    /**
     * 获得员工姓名
     */
    @Test
    public void test3(){
        String hql="select e.ename from Emp e";
        Query query=session.createQuery(hql);
        List<String> enames=query.list();
        for (String s:enames){
            System.out.println(s);
        }
    }
    /**
     * 获得员工姓名和工资
     */
    @Test
    public void test4(){
        String hql="select e.ename,e.salary from Emp e";
        Query query=session.createQuery(hql);
        List<Object[]> objects=query.list();
        for (Object[] objects1:objects){
            System.out.println(objects1[0]+" "+objects1[1]);
        }
    }
    /**
     * 获得员工姓名和工资(动态构造对象)
     */
    @Test
    public void test5(){
        String hql="select new Emp(e.ename,e.salary) from Emp e";
        Query query=session.createQuery(hql);
        List<Emp> emps=query.list();
        for(Emp emp:emps){
            System.out.println(emp.getEname()+" "+emp.getSalary());
        }
    }
    /**
     * 根据员工姓名和入职日期查询员工信息(根据位置绑定参数)
     */
    @Test
    public void test6(){
        String hql="from Emp e where e.ename like ? and e.hiredate >?" ;
        Query query=session.createQuery(hql);
        query.setString(0,"%张%");
        query.setString(1,"2017-7-1");
        List<Emp> emps=query.list();
        for (Emp emp:emps){
            System.out.println(emp.getEname()+" "+emp.getHiredate());
        }
    }
    /**
     * 根据员工姓名和入职日期查询员工信息(根据名称绑定参数)
     */
    @Test
    public void test8(){
        String hql="from Emp e where e.ename like :ename and e.hiredate > :hiredate" ;
        Query query=session.createQuery(hql);
        query.setString("ename","%张%");
        query.setString("hiredate","2017-7-1");
        List<Emp> emps=query.list();
        for (Emp emp:emps){
            System.out.println(emp.getEname()+" "+emp.getHiredate());
        }
    }
    /**
     * 根据员工姓名和入职日期查询员工信息(根据properties绑定参数)
     */
    @Test
    public void test9() throws ParseException {
        String hql="from Emp e where e.ename like :ename and e.hiredate > :hiredate" ;
        Query query=session.createQuery(hql);
        DateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd");
        Date date=dateFormat.parse("2017-7-1");
        Map queryProperties=new HashMap();
        queryProperties.put("ename","%张%");
        queryProperties.put("hiredate",date);
        query.setProperties(queryProperties);
        List<Emp> emps=query.list();
        for (Emp emp:emps){
            System.out.println(emp.getEname()+" "+emp.getHiredate());
        }
    }

    /**
     * 统计员工表中的部门数
     */
    @Test
    public void test10(){
        String hql="select count(e.dept) from Emp e";
        Query query=session.createQuery(hql);
        Long count= (Long) query.uniqueResult();
        System.out.println(count);
    }

    /**
     * 查出最高与最低工资
     */
    @Test
    public void test11(){
        String hql="select max(salary),min(salary) from Emp ";
        Query query=session.createQuery(hql);
        List<Object[]> objects=query.list();
        for(Object[] objects1:objects){
            System.out.println(objects1[0]+" "+objects1[1]);
        }
    }
    /**
     * 查出总工资与平均工资
     */
    @Test
    public void test12(){
        String hql="select sum(salary),avg(salary) from Emp";
        Query query=session.createQuery(hql);
        List<Object[]> objects=query.list();
        for (Object[] objects1:objects){
            System.out.println(objects1[0]+" "+objects1[1]);
        }
    }

    /**
     * 按工资、入职日期排序
     */
    @Test
    public void test13(){
        String hql="from Emp order by salary asc,hiredate desc";
        Query query=session.createQuery(hql);
        List<Emp> emps=query.list();
        for(Emp emp:emps){
            System.out.println(emp.getEname()+" "+emp.getSalary()+" "+emp.getHiredate());
        }
    }

    /**
     * 统计各部门人数大于1的部门及其人数
     */
    @Test
    public void test14(){
        String hql="select dept.deptid,count(empid) from Emp group by dept.deptid having count(empid)>1";
        Query query=session.createQuery(hql);
        List<Object[]> objects=query.list();
        for(Object[] obects1:objects){
            System.out.println(obects1[0]+" "+obects1[1]);
        }
    }

    /**
     * 分布查询员工信息
     */
    @Test
    public void test15(){
        String hql="from Emp ";
        Query query=session.createQuery(hql);
        query.setFirstResult(1);
        query.setMaxResults(2);
        List<Emp> emps=query.list();
        for (Emp emp:emps){
            System.out.println(emp.getEname());
        }
    }

    /**
     * 查询工资最高的员工
     */
    @Test
    public void test16(){
        String hql="select e from Emp e where e.salary=(select max (salary) from Emp)";
        Query query=session.createQuery(hql);
        List<Emp> emps=query.list();
        for(Emp emp:emps){
            System.out.println(emp.getEname()+" "+emp.getSalary());
        }
    }
    public void tearDown(){
        session.close();
    }
}
